<?php
/* ******************************
 *	letterA aJax actions
 * ******************************
 */

include('../../includes/classes/core.php');
$action		= $_REQUEST['act'];
$error		= '';
$data		= '';
$user_id	= $_SESSION['USERID'];
switch ($action) {
    case 'get_add_page':
		$page		= GetPage();
		$data		= array('page'	=> $page);

        break;
    case 'get_edit_page':
	    $letter_id	= $_REQUEST['id'];
		$page		= GetPage(GetOrder($letter_id));

        $data 		= array('page'	=> $page);

        break;
    case 'get_list':
	    $count = $_REQUEST['count'];
	    $hidden = $_REQUEST['hidden'];

	    $rResult = mysql_query("SELECT		`a_letter`.`id`,
											`a_letter`.`request_date`,
	    									CONCAT('№', ' ა  - ', `a_letter`.`id`),
											CONCAT(`client`.`name`, '(', `client_objects`.`name`, ')'),
											`a_letter`.`planned_end_date`,
											`a_letter`.`fact_end_date`,
											`request_status`.`name`,
	    									`a_letter`.`status`,
											`warehouse_status`.`name`	                                                
								FROM		`a_letter`
								LEFT JOIN	`client_objects` ON `a_letter`.`client_object_id` = `client_objects`.`id`
								LEFT JOIN	`client` ON `client`.`id` = `client_objects`.`client_id`
								LEFT JOIN	`request_status` ON `a_letter`.`status` = `request_status`.`id`
								LEFT JOIN	`warehouse` ON `warehouse`.`a_letter_id` = `a_letter`.`id`
	    						LEFT JOIN	`warehouse_status` ON `warehouse_status`.`id` = `warehouse`.`status`	                            
								WHERE		`a_letter`.`actived` = 1 AND  warehouse.client_object_id>0 $_REQUEST[filter]
	    						ORDER BY 	`a_letter`.`id` DESC");

		$data = array(
			"aaData"	=> array()
		);

		if(!$rResult){
			$error = 'Invalid query: ' . mysql_error();
		}else{
			while ( $aRow = mysql_fetch_array( $rResult ) )
			{
				$row = array();
				for ( $i = 0 ; $i < $count ; $i++ )
				{
					/* General output */
					$row[] = $aRow[$i];
					if( $i == ($count-1) ){
						if( $aRow[$i+1] < 5 ){
							$row[$i] = $aRow[$i+2];
						}
					}
					if($i == ($count - 1)){
						$row[] = '<input type="checkbox" name="check_' . $aRow[$hidden] . '" class="check" value="' . $aRow[$hidden] . '" />';
					}

				}
				$data['aaData'][] = $row;
			}

		}

        break;
    case 'disable':
    	DisableAletter($_REQUEST['id']);
    	break;
    case 'save_latter':
		$id = $_REQUEST['id'];
		$arr = array(
				"warehouse_id"			=> $_REQUEST['wid'],
				"source"				=> $_REQUEST['s'],
				"request_date"			=> $_REQUEST['rd'],
				"finish_date"			=> $_REQUEST['fd'],
				"end_date"				=> $_REQUEST['ed'],
				"planned_end_date"		=> $_REQUEST['ped'],
				"priority"				=> $_REQUEST['pri'],
				"fact_end_date"			=> $_REQUEST['fed'],
				"responsible_person"	=> $_REQUEST['rp'],
				"client_object"			=> GetClientObjectID($_REQUEST['co']),
				"requester"				=> $_REQUEST['req'],
				"new_requester"			=> $_REQUEST['nreq'],
				"receiver"				=> $_REQUEST['rec'],
				"car_id"				=> $_REQUEST['ci'],
		        "trans_price"			=> $_REQUEST['price'],  
				"comment"				=> $_REQUEST['c']
		);

		SaveLatter($id, $user_id, $arr);

        break;
	case 'check_list':
		$list_id 		= $_REQUEST['id'];
		$check			= ChackList($list_id);
		if($check){
			$error = 'სერვისების სია ცარიელია!';
		}

        break;
    case 'get_local_id':

		$data = array('local_id' => GetLocalID() );

		break;
	case 'get_client_id':
        $client_name	= $_REQUEST['cn'];
        $client_id		= GetClientID($client_name);

        $data = array('client_id' => $client_id);

        break;
    case 'get_client_object_id':
		$client_name	= $_REQUEST['cn'];
		$client_obj_id	= GetClientObjectID($client_name);

		$data = array('client_obj_id' => $client_obj_id);

        break;
    case 'get_overhead_id':
	    $letter_id		= $_REQUEST['id'];
		$overhead_id	= GetOverheadID($letter_id);
		ChangeLocation($letter_id);
		$data = array('overhead_id' => $overhead_id);

        break;
    case 'get_client_persons':
		$client_name	= $_REQUEST['cn'];
		$client_persons	= GetClientPersons($client_name, '');

		$data = $client_persons;

        break;
	case 'check':
		$data		= array('message'	=> check());

		break;
    case 'clear_db':
		ClearDB();

        break;
    case 'show_planed_end_date':
    	$local_id	= $_REQUEST['local_id'];
    	$page		= GetPlanEndDate($local_id);
    	$data		= array('page'	=> $page);

    	break;
	case 'show_same_letters':
		$page		= GetSameLetters();
		$data		= array('page'	=> $page);

		break;
	case 'get_planed_end_date':
		$count = $_REQUEST['count'];
		$hidden = $_REQUEST['hidden'];
		$rResult = mysql_query("SELECT		`A`.`end_date` as `end_date`,
											COALESCE(CONCAT(DATE(`A`.`end_date`),'  -  ა-',A.a_letter_id),
													 CONCAT(DATE(`A`.`end_date`),'  -  დ-',A.d_letter_id),
													 CONCAT(DATE(`A`.`end_date`),'  -  R-',A.id)


                                            ) as `date`,

											DATE_FORMAT( `A`.`end_date`, '%k:%i') as `time`,
											TIME_FORMAT( TIMEDIFF( A.end_date ,  B.end_date) , '%k:%i') as freeTime,
											COUNT(  `warehouse_detail`.`id` ) as `count` ,
											CONCAT(`client`.`name`,'(',`client_objects`.`name`, ')') AS `client`
								FROM		`warehouse` as `A`
								LEFT JOIN 	warehouse B ON B.id =
											(
												SELECT c.id FROM warehouse  as c WHERE	c.end_date <= A.end_date AND c.id != A.id  ORDER BY		`c`.`end_date`  DESC LIMIT 1
											)
								LEFT JOIN	`warehouse_detail` ON `warehouse_detail`.`warehouse_id` = `A`.`id`
								LEFT JOIN	`client_objects` ON `client_objects`.`id` = `A`.`client_object_id`
								LEFT JOIN	`client` ON `client_objects`.`client_id` = `client`.`id`
								WHERE		`A`.`status` <=3 AND DATE(A.end_date)>=DATE(NOW())
								GROUP BY 	`A`.`id`
								ORDER BY	`A`.`end_date` ");

		$data = array(
				"aaData"	=> array()
		);

		if(!$rResult){
			$error = 'Invalid query: ' . mysql_error();
		}else{
			while ( $aRow = mysql_fetch_array( $rResult ) )
			{
				$row = array();
				for ( $i = 0 ; $i < $count ; $i++ )
				{
						if( $i == 3){
							/* General output */
							$row[] ='<p style="color: green;"><b>'. $aRow[$i].'</b></p>';
						}else{
							/* General output */
							$row[] = $aRow[$i];
						}

				}
				$data['aaData'][] = $row;
			}
		}

		break;
	case 'get_same_letters':
		$count 		= $_REQUEST['count'];
		$hidden 	= $_REQUEST['hidden'];
		$clientcobj = $_REQUEST['cobj'];

		$rResult = mysql_query("SELECT		warehouse.id,
											CASE WHEN NOT ISNULL(`warehouse`.`d_letter_id`) THEN CONCAT( '№: დ-',`warehouse`.`d_letter_id`)
												WHEN NOT ISNULL(`warehouse`.`a_letter_id`) THEN CONCAT( '№: ა-',`warehouse`.`a_letter_id`)
											END as `letter`,
											warehouse.end_date,
											CONCAT(`client`.`name`,'(',`client_objects`.`name`, ')') AS `client`,
											CONCAT(`client_objects`.address_name , ' ', `address_types`.`name` , ' ', `client_objects`.`address_number` ) AS `startAddress`,
											districts.`name`
								FROM			warehouse
								LEFT JOIN	`client_objects` ON `client_objects`.`id` = `warehouse`.`client_object_id`
								LEFT JOIN	`client` ON client_objects.client_id = client.id
								LEFT JOIN	`postal_codes` ON `postal_codes`.`id` = `client_objects`.`postal_code_id`
								LEFT JOIN	`districts` ON 	`districts`.`id` = `postal_codes`.`district_id`
								LEFT JOIN	`address_types` ON `address_types`.`id` = `client_objects`.`address_type`
 								LEFT JOIN a_letter ON warehouse.a_letter_id=a_letter.id  AND a_letter.actived=1
								WHERE		`warehouse`.`status` <=5 && ( NOT ISNULL(warehouse.a_letter_id) || NOT ISNULL(warehouse.d_letter_id) ) && districts.id =
								(
										SELECT `districts`.`id`
										FROM `client_objects`
										LEFT JOIN	`client` ON client_objects.client_id = client.id
										LEFT JOIN	`postal_codes` ON `postal_codes`.`id` = `client_objects`.`postal_code_id`
										LEFT JOIN	`districts` ON 	`districts`.`id` = `postal_codes`.`district_id`
										WHERE CONCAT(`client`.`name`,'(',`client_objects`.`name`, ')') = '$clientcobj'
								) AND warehouse.end_date > ADDDATE(NOW(),INTERVAL 1 HOUR)");

		$data = array(
				"aaData"	=> array()
		);

		if(!$rResult){
			$error = 'Invalid query: ' . mysql_error();
		}else{
			while ( $aRow = mysql_fetch_array( $rResult ) )
			{
				$row = array();
				for ( $i = 0 ; $i < $count ; $i++ )
				{
					/* General output */
					$row[] = $aRow[$i];
				}
				$data['aaData'][] = $row;
			}
				}

		break;
	case 'check_p_end_date':
		$id 				= $_REQUEST['id'];
		$planned_end_date	= $_REQUEST['ped'];
		$data		 		= array('answ'	=> CheckpEndDate($id, $planned_end_date) );

		break;
	case 'recomendation':
		$count 		= $_REQUEST['count'];
		$hidden 	= $_REQUEST['hidden'];
		$date		= $_REQUEST['date'];
		$est_time	= $_REQUEST['est_time'];
	//	$rResult = mysql_query
		echo("SELECT		`A`.`id`,
											DATE (`A`.`planned_end_date`) AS 'date',
											ADDTIME (TIME(`A`.`planned_end_date`), '$est_time' ) AS 'start',
											TIMEDIFF (TIME(`B`.`planned_end_date`) ,TIME_FORMAT(SEC_TO_TIME( SUM(P.job_done_time * 60 / ( SELECT	COUNT(persons.id) FROM persons WHERE	persons.c_active = 1) )  ),'%H:%i:%s') )AS 'end',
											TIMEDIFF (TIMEDIFF (TIME(`B`.`planned_end_date`) ,TIME_FORMAT(SEC_TO_TIME( SUM(P.job_done_time * 60 / ( SELECT	COUNT(persons.id) FROM persons WHERE	persons.c_active = 1) )  ),'%H:%i:%s') ), ADDTIME (TIME(`A`.`planned_end_date`), '$est_time' )) AS 'diff'
								FROM		`a_letter` AS `A`
								LEFT JOIN	`a_letter` AS `B` ON `B`.`id` =
								(
									SELECT `C`.`id` FROM `a_letter`  as `C` WHERE `C`.`planned_end_date` >= `A`.`planned_end_date` AND `C`.`id` != `A`.`id`  ORDER BY  `C`.`planned_end_date`  ASC LIMIT 1
								)
                                LEFT JOIN     warehouse ON warehouse.a_letter_id=`B`.`id`
							    LEFT JOIN     warehouse_detail  AS `D` ON 	D.warehouse_id=warehouse.id
								LEFT JOIN    `production` AS `P` ON `P`.id = `D`.prod_id
								WHERE     	DATE(`A`.`planned_end_date`) = '$date' && TIMEDIFF (TIMEDIFF (TIME(`B`.`planned_end_date`) ,TIME_FORMAT(SEC_TO_TIME(  '00:30:00' ),'%H:%i:%s') ), ADDTIME (TIME(`A`.`planned_end_date`), '00:23' )) > '$est_time'
								GROUP BY	`B`.`id`
								ORDER BY	`A`.`planned_end_date` ASC
				");

		$data = array (
				"aaData" => array ()
		);

		if (! $rResult) {
			$error = 'Invalid query: ' . mysql_error ();
		} else {
			while ( $aRow = mysql_fetch_array ( $rResult ) ) {
				$row = array ();
				for($i = 0; $i < $count; $i ++) {
					/* General output */
					$row [] = $aRow [$i];
				}
				$data ['aaData'] [] = $row;
			}
			//array_pop($data ['aaData']);
		}
		break;
	case 'get_end_date':
		$request_date		= $_REQUEST['r_d'];
		$client				= $_REQUEST['client'];
		$letter_id			= $_REQUEST['letter_id'];
		$data['end_date']	= GetEndDate($request_date, $client, $letter_id);
		$data['pln_date']	= GetplnDate($_REQUEST[clien_obj], $data['end_date']);
		break;
	case 'empty-form':
	    $data[page]=emptypage();
	    break;
    default:
       $error = 'Action is Null';
}

$data['error'] = $error;

echo json_encode($data);


/* ******************************
 *	Order Functions
 * ******************************
 */
function emptypage(){
 	$data = '
	<div id="dialog-form">
	    <fieldset>
	    	<legend>ძირითადი ინფორმაცია</legend>
 	          <table> <tr>
                <td>მძღოლი</td>  <td>მანქანა;</td> <td>რაოდენობა</td> </tr><tr>
                <td><select style="display: inline-block;" id="receiver_" class="idls">'. GetPersons() .'</select></td>
                <td><select style="display: inline-block;" id="car_id_"   class="idls">'. GetAutoNumber() .'</select></td>
                <td><input  style="display: inline-block;" type="number" class="idls" id="quantity_" min="1" max="30">   </td>   </tr>
                </table>
        </fieldset>
    </div>
    ';
	return $data;

}

function GetplnDate($latter,$end_date){
 $res = mysql_fetch_array(mysql_query("
                            SELECT	 IFNULL(MIN(warehouse.end_date), ADDDATE('$end_date',INTERVAL -1 HOUR))
                FROM			warehouse
                LEFT JOIN	`client_objects` ON `client_objects`.`id` = `warehouse`.`client_object_id`
                LEFT JOIN	`client` ON client_objects.client_id = client.id
                LEFT JOIN	`postal_codes` ON `postal_codes`.`id` = `client_objects`.`postal_code_id`
                LEFT JOIN	`districts` ON 	`districts`.`id` = `postal_codes`.`district_id`
                LEFT JOIN	`address_types` ON `address_types`.`id` = `client_objects`.`address_type`
                LEFT JOIN  a_letter ON warehouse.a_letter_id=a_letter.id AND a_letter.actived=1 AND ISNULL(a_letter.b_letter_overhead_id) AND a_letter.id > 4000
                LEFT JOIN  d_letter ON warehouse.d_letter_id=d_letter.id AND d_letter.actived=1 AND ISNULL(d_letter.e_letter_overhead_id) AND d_letter.id > 400
                WHERE		`warehouse`.`status` <=5 && ( NOT ISNULL(a_letter.id) || NOT ISNULL(d_letter.id) ) && districts.id =
                		(SELECT `districts`.`id`
                		FROM 	`client_objects`
                		 JOIN	`client` ON client_objects.client_id = client.id
                		 JOIN	`postal_codes` ON `postal_codes`.`id` = `client_objects`.`postal_code_id`
                		 JOIN	`districts` ON 	`districts`.`id` = `postal_codes`.`district_id`                			
                		WHERE client_objects.id=$latter LIMIT 1)
                AND warehouse.end_date BETWEEN ADDDATE(NOW(),INTERVAL 1 HOUR) AND ADDDATE('$end_date',INTERVAL -1 HOUR)"));
 return $res[0];
                        
    
}

function GetEndDate($request_date, $client, $letter_id){

	$res = mysql_fetch_assoc(mysql_query("SELECT COUNT(*) as quantity
                                        	FROM warehouse_detail
                                        	WHERE warehouse_id=$_REQUEST[warehouse_id] AND actived=1"));

	$res1 = mysql_query("SELECT 	client_timetable.id AS `id`
						FROM    	client_timetable
						WHERE   	client_timetable.client_id = $client AND (NOT ISNULL(request_start_time) OR NOT ISNULL(request_end_time)) AND ($res[quantity] BETWEEN request_min_quantity AND request_max_quantity) AND (TIME('$request_date') > request_start_time OR TIME('$request_date') <= request_end_time)");

	if (mysql_num_rows($res1) > 0) {
		$res1 = mysql_fetch_assoc($res1);
		$mysqlResult = mysql_fetch_assoc(mysql_query("	SELECT CASE
																	WHEN DAYOFWEEK( DATE_ADD(DATE('$request_date'), INTERVAL days DAY)) = 1 THEN DATE_FORMAT(CONCAT(DATE_ADD(DATE('$request_date'), INTERVAL days + 1 DAY), ' ', cartridge_done_time), '%Y-%m-%d %k:%i')
																	WHEN DAYOFWEEK( DATE_ADD(DATE('$request_date'), INTERVAL days DAY)) = 7  AND (cartridge_done_time < '10:00:00' OR  cartridge_done_time > '14:00:00')
																			THEN DATE_FORMAT(CONCAT(DATE_ADD(DATE('$request_date'), INTERVAL days + 2 DAY), ' ', cartridge_done_time), '%Y-%m-%d %k:%i')
																	ELSE DATE_FORMAT(CONCAT(DATE_ADD(DATE('$request_date'), INTERVAL days DAY), ' ', cartridge_done_time), '%Y-%m-%d %k:%i')
																END  AS `time`
														FROM 	`client_timetable`
														WHERE 	client_timetable.client_id = $client AND client_timetable.id = $res1[id]"));
		return $mysqlResult['time'];
	}else {
		$mysqlResult = mysql_fetch_assoc( mysql_query("	SELECT  CASE
																	WHEN DAYOFWEEK( ADDTIME('$request_date' , cartridge_done_time)) = 1 THEN  DATE_FORMAT(DATE_ADD(ADDTIME('$request_date', cartridge_done_time), INTERVAL 1 DAY), '%Y-%m-%d %k:%i')
																	WHEN DAYOFWEEK( ADDTIME('$request_date' , cartridge_done_time)) = 7 AND (TIME(ADDTIME('$request_date', cartridge_done_time)) < '10:00:00' OR TIME(ADDTIME('$request_date', cartridge_done_time))) > '14:00:00'  THEN DATE_FORMAT(DATE_ADD(ADDTIME('$request_date', cartridge_done_time), INTERVAL 2 DAY), '%Y-%m-%d %k:%i')
																	ELSE DATE_FORMAT(ADDTIME('$request_date' , cartridge_done_time), '%Y-%m-%d %k:%i')
																END  AS `time`
														FROM 	`client_timetable`
														WHERE 	client_timetable.client_id = $client"));
		return $mysqlResult['time'];
	}



}
function DisableAletter($a_letter_id)
{

	$warehouse_id = mysql_fetch_assoc(mysql_query("SELECT 	warehouse.id
												  FROM		warehouse
												  WHERE		warehouse.a_letter_id = $a_letter_id"));

	mysql_query("	UPDATE 		a_letter
					SET 		actived = 0
					WHERE 		id = $a_letter_id ");

	mysql_query("	UPDATE 		warehouse_detail
					SET			warehouse_detail.actived = 0
					WHERE		warehouse_detail.warehouse_id = $warehouse_id[id]");

	mysql_query("	UPDATE 		warehouse
					SET        	warehouse.actived = 0
					WHERE		warehouse.id = $warehouse_id[id]");

}
function ChangeLocation($letter_id){
	$csql = mysql_query("	SELECT		warehouse_detail.barcode as `barcode`,
										warehouse.client_object_id  as `location`
							FROM		a_letter
							LEFT JOIN	warehouse ON warehouse.a_letter_id = a_letter.id
							LEFT JOIN	warehouse_detail ON warehouse_detail.warehouse_id = warehouse.id
							WHERE		a_letter.id = '$letter_id'");

	while ( $aRow = mysql_fetch_array( $csql ) ){
		mysql_query("	UPDATE 	`reserve`
						SET 	`reserve`.`object_id` = '$aRow[location]'
						WHERE	`reserve`.`barcode` = '$aRow[barcode]' ");
	}
}

function CheckpEndDate($id, $planned_end_date){
	$mysqlResult = mysql_fetch_assoc( mysql_query("	SELECT	`a_letter`.`id`
													FROM	`a_letter`
													WHERE	`a_letter`.`id` != '$id' AND `a_letter`.`planned_end_date` = '$planned_end_date' AND `a_letter`.`status`!= 4"));
	if( $mysqlResult ){
		return 1;
	}
	return 0;
}

function ClearDB()
{
	$req1 = mysql_query("SELECT	`id`
						 FROM	`warehouse_detail`
						 WHERE	`warehouse_id` NOT IN (SELECT `id` FROM `warehouse`) ");

	while( $res1 = mysql_fetch_assoc($req1)){
		mysql_query("DELETE
					FROM
						`warehouse_detail`
					WHERE
						`id` = $res1[id] AND `warehouse_detail.user_id`=$_SESSION[USERID]");
	}

}

function SaveLatter($id, $user_id, $arr)
{
	GLOBAL $error;
	$result = mysql_query("INSERT IGNORE INTO `a_letter` (`id`) VALUES ($id)");
	if(!$result){
		$error = 'Invalid query: ' . mysql_error();
	}else{
		$c_date = date('Y-m-d H:i:s');

		$requester = $arr['requester'];

		if(!empty($arr['new_requester'])){
			mysql_query("	INSERT INTO `client_object_persons`
								(`user_id`, `client_object_id`, `name`)
							VALUES
								($user_id, $arr[client_object], '$arr[new_requester]')");

			$requester = mysql_insert_id();
		}

		$rSQL = mysql_fetch_array(mysql_query("SELECT	`a_letter`.`status` as `status`,
														`warehouse`.`status` as `wstatus`
											from 		`a_letter`
											LEFT JOIN	`warehouse` ON `warehouse`.`a_letter_id` = `a_letter`.`id`
											WHERE		`a_letter`.`id`  = '$id'"));
		$status = $rSQL['status'];
		$wstatus = $rSQL['wstatus'];
		if( empty( $status ) ){
			$status = 1;
			$wstatus = 0;
		}
		$ow='';
		if ($_REQUEST[ovch]=='true') {
		    $ow="overhead_id	= 0,
		         b_letter_overhead_id =0, ";
		    
		}

		mysql_query("UPDATE
		    			`a_letter`
					 SET
					 	`user_id`				= $user_id,
						`source`				= '$arr[source]',
						`client_object_id`		= '$arr[client_object]',
						`request_date`			= '$arr[request_date]',
						`start_date`			= '$c_date',
						`end_date`				= '$arr[end_date]',
						`planned_end_date`		= '$arr[planned_end_date]',
						`fact_end_date`			= '$arr[fact_end_date]',
						`finish_date`			= '$arr[finish_date]',
						`requester`				= '$requester',
						`receiver`				= '$arr[receiver]',
						`responsible_person`	= '$arr[responsible_person]',
						`status`				= $status,
						`car_id`				= '$arr[car_id]',
						`a_trans_price`         = '$arr[trans_price]',
						$ow
						`comment`				= '$arr[comment]',
						`priority`				= $arr[priority],
						`actived`               = 1
					 WHERE
						`id` = '$id'");

			$result1 = mysql_query("INSERT IGNORE INTO `warehouse` (`id`) VALUES ( $arr[warehouse_id] )");
			if(!$result1){
				$error = 'Invalid query: ' . mysql_error();
			}else{
				mysql_query("UPDATE
									`warehouse`
							SET
									`user_id`			= '$user_id',
									`a_letter_id`		= '$id',
									`start_date`		= '$c_date',
									`client_object_id`	= '$arr[client_object]',
									`end_date`			= '$arr[planned_end_date]',
									`status`			= '$wstatus'
							WHERE
									id					= '$arr[warehouse_id]'");
			}

	}
}

function ChackList($list_id)
{
	$check = false;
	$req = mysql_query("SELECT	`id`
						FROM	`warehouse_detail`
						WHERE	`warehouse_id` = '$list_id'");

	if (mysql_num_rows($req) == 0){
		$check = true;
	}

	return $check;
}

function GetLocalID()
{
	$ids = array();
	GLOBAL $db;
	$ids[] = $db->increment('a_letter');
	$ids[] = $db->increment('warehouse');
	return $ids;
}

function GetClientID($client_name)
{
	$res = mysql_fetch_assoc(mysql_query("	SELECT		`client`.`id`
											FROM		`client_objects`
											LEFT JOIN	`client` ON `client`.`id` = `client_objects`.`client_id`
											WHERE		CONCAT(`client`.`name`,'(',`client_objects`.`name`, ')') = '$client_name'"));
	return $res['id'];
}

function GetClientObjectID($client_name)
{
	$res = mysql_fetch_assoc(mysql_query("	SELECT     `client_objects`.`id`
											FROM       `client_objects`
											LEFT JOIN  `client` ON `client`.`id` = `client_objects`.`client_id`
											WHERE      CONCAT(`client`.`name`,'(',`client_objects`.`name`, ')') = '$client_name'"));
	return $res['id'];
}

function GetOverheadID($letter_id)
{
	$res = mysql_fetch_assoc(mysql_query("	SELECT     `overhead_id`
											FROM       `a_letter`
											WHERE      `id` = '$letter_id'"));
	return $res['overhead_id'];
}

function GetSource($point)
{
	$data = '';
	$req = mysql_query("SELECT	`id`, `name`
						FROM	`request_sources`");

	if($point == ''){
		$data = '<option value="0" selected="selected"></option>';
	}

	while( $res = mysql_fetch_assoc($req)){
		if($res['id'] == $point){
			$data .= '<option value="' . $res['id'] . '" selected="selected">' . $res['name'] . '</option>';
		} else {
			$data .= '<option value="' . $res['id'] . '">' . $res['name'] . '</option>';
		}
	}
	return $data;
}

function GetPersons($person_id)
{
	$data = '';
	$req = mysql_query("SELECT `id`, `name`
						FROM `persons`
						WHERE `persons`.`position` !=13 && `persons`.actived != 0");

	$data .= '<option value="' . 0 . '" selected="selected">' . '' . '</option>';

	while( $res = mysql_fetch_assoc($req)){
		if($res['id'] == $person_id){
			$data .= '<option value="' . $res['id'] . '" selected="selected">' . $res['name'] . '</option>';
		} else {
			$data .= '<option value="' . $res['id'] . '">' . $res['name'] . '</option>';
		}
	}
	return $data;
}

function GetClientPersons($client_name, $point)
{
	$data[client_persons] .= '<option value="" selected="selected"></option>';
	$req = mysql_query("SELECT		`client_object_persons`.`id`,
									`client_object_persons`.`name`,
	    							 client_objects.id as o_id,  
                                     transporter_company_id
						FROM		`client_objects`
						LEFT JOIN	`client_object_persons` ON `client_objects`.`id` = `client_object_persons`.`client_object_id`
						LEFT JOIN    client ON client.id = client_objects.client_id
						WHERE		CONCAT(`client`.`name`,'(',`client_objects`.`name`, ')') = '$client_name'");

	while( $res = mysql_fetch_assoc($req)){
		if($res['id'] == $point){
			$data[client_persons] .= '<option value="' . $res['id'] . '" selected="selected">' . $res['name'] . '</option>';
		} else {
			$data[client_persons] .= '<option value="' . $res['id'] . '">' . $res['name'] . '</option>';
		}
		$data[trans]=$res['transporter_company_id'];
		$data[id]= $res['o_id'];
	}
	return $data;
}

function GetPriority($priority){
    if ($priority=='') {
        $priority=1;
    }
	$data = '';
	$req = mysql_query("SELECT	`id`,
								`name`
						FROM	`priority_catalog`");
	while( $res = mysql_fetch_assoc($req)){
		if($res['id'] == $priority){
			$data .= '<option value="' . $res['id'] . '" selected="selected">' . $res['name'] . '</option>';
		} else {
			$data .= '<option value="' . $res['id'] . '">' . $res['name'] . '</option>';
		}
	}

	return $data;
}

function GetAutoNumber($point)
{
	$data = '';
	$req = mysql_query("SELECT	`id`, `name`
						FROM	`object`
						WHERE	`type` = 3 && `object`.actived != 0"); // <<<<---------------------Type!!!
	$data .= '<option value="' . 0 . '" selected="selected">' . '' . '</option>';
	while( $res = mysql_fetch_assoc($req)){
		if($res['id'] == $point){
			$data .= '<option value="' . $res['id'] . '" selected="selected">' . $res['name'] . '</option>';
		} else {
			$data .= '<option value="' . $res['id'] . '">' . $res['name'] . '</option>';
		}
	}

	return $data;
}

function CreateTime($time){
	$houers = '00';
	$minutes = '00';
	while(true){
		if( $time < 60 ){
			$minutes =(int)$minutes + (int)$time;
			break;
		}else{
			$houers = '0'.(int)$houers + 1;

			$time = (int)$time - 60;
		}
	}
	if (strlen($minutes) == 1) {
		$minutes = '0'.$minutes;
	}
	return '<h1  style="display: inline; color: #FF0000;"><b id="est_time">'.$houers.':'.$minutes.'</b></h1>';
}

function GetEstimatedTime($local_id){
	$result = mysql_fetch_assoc( mysql_query("	SELECT		FORMAT( (SUM(production.job_done_time) / ( SELECT	COUNT(persons.id) FROM persons WHERE	persons.c_active = 1) ), 0) as `time`
                                                                												FROM warehouse_detail
                                                                LEFT JOIN	production ON production.id = warehouse_detail.prod_id
                                                                WHERE warehouse_detail.actived = 1 AND warehouse_id= '$_REQUEST[warehouse_id]'"));
return CreateTime($result['time']);
}

function GetOrder($letter_id)
{
    $res = mysql_fetch_assoc(mysql_query("	SELECT		`a_letter`.`id`,
														`a_letter`.`overhead_id`,
														`a_letter`.`source`,
														`a_letter`.`request_date`,
														`a_letter`.`start_date`,
    													`a_letter`.`fact_end_date`,
														`a_letter`.`planned_end_date`,
                                                         client_objects.id AS client_objects_id,
                                                        rs_wdsl_status.end_date as finish_date,
														`a_letter`.`end_date`,
														`a_letter`.`job_start_date`,
														`a_letter`.`job_end_date`,
                                                         a_letter.a_trans_price,
														 b_trans_price,
                                                         transporter_company_id,   	
														`client_object_persons`.`id` AS `object_person_id`,
														`a_letter`.`receiver`,
														`a_letter`.`responsible_person`,
														`a_letter`.`car_id`,
														`a_letter`.`comment`,
														CONCAT(`client`.`name`,'(',`client_objects`.`name`, ')') AS `object_name`,
														`a_letter`.`priority` as `priority`,
    													`warehouse`.`id` as `warehouse_id`
											FROM		`a_letter`
											LEFT JOIN	`client_object_persons` ON `a_letter`.`client_object_id` = `client_object_persons`.`client_object_id` && `a_letter`.`requester` = `client_object_persons`.`id`
											LEFT JOIN	`client_objects` ON `a_letter`.`client_object_id` = `client_objects`.`id`
											LEFT JOIN	`client` ON `client`.`id` = `client_objects`.`client_id`
											LEFT JOIN	`warehouse` ON `warehouse`.`a_letter_id` = `a_letter`.`id`
                                            LEFT JOIN      rs_wdsl_status ON rs_wdsl_status.id=a_letter.overhead_id
											WHERE		`a_letter`.`id` = '$letter_id'"));
	return $res;
}

function GetSameLetters(){
	$data = '<div id="dialog-form">
			    <fieldset>
					<legend>მსგავსი ბარათები</legend>
					    <div class="inner-table">
						    <div id="dt_example" class="ex_highlight_row">
						        <div id="container" class="overhead_container">
						        	<div id="button_area" class="inner_buttons">
						        	</div>
						            <div id="dynamic">
						                <table class="display" id="same_letter_list">
						                    <thead>
						                        <tr id="datatable_header">
						                            <th>ID</th>
						                            <th style="width: 100px;">ბარათი</th>
						                            <th class="min" title="შესრულების გეგმიური დრო">შესრ.გეგ.დრო</th>
						                            <th style="width: 100%">ორგანიზაცია</th>
													<th class="min">მისამართი</th>
													<th class="min">უბანი</th>
						                        </tr>
						                    </thead>
						                    <thead>
						                        <tr class="search_header">
						                            <th class="colum_hidden">
													<input type="text" name="search_id" value="ფილტრი" class="search_init" />
						                            </th>
						                            <th>
														<input type="text" name="search_id" value="ფილტრი" class="search_init" />
						                            </th>
						                            <th>
						                            	<input type="text" name="search_date" value="ფილტრი" class="search_init" />
													</th>
													<th>
						                            	<input type="text" name="search_client" value="ფილტრი" class="search_init" />
													</th>
													<th>
						                            	<input type="text" name="search_address" value="ფილტრი" class="search_init" />
													</th>
													<th>
						                            	<input type="text" name="search_distrinct" value="ფილტრი" class="search_init" />
													</th>
						                        </tr>
						                    </thead>
						                </table>
						            </div>
						        </div>
						    </div>
						</div>
		        </fieldset>
			<div>';
	return $data;
}

function GetPlanEndDate($local_id){
	$data = '<div id="dialog-form">
			    <fieldset>
					<legend>ძირითადი ინფორმაცია</legend>
					<table>
					<tr>
						<td style=" padding-top: 5px; ">
							ბლანკის შესასრულებელი სავარაუდო დრო :
						</td>
						<td>
							'.GetEstimatedTime($local_id).'
						</td>
						<td style=" padding-top: 6px; padding-left: 2px; ">
							სთ
						</td>
					</tr>
					</table>
				</fieldset>
				<fieldset>
					<legend>რეკომენდირებული შუალედები</legend>
					<table>
					<tr>
						<td style=" padding-top: 5px; ">
							თარიღი :
						</td>
						<td>
							<input id="day_date" type="text" class="idle date_time" onblur="this.className=\'idle date_time\'" onfocus="this.className=\'activeField date_time\'" value="" />
						</td>
					</tr>
					</table>
					<div class="inner-table">
						    <div id="dt_example1" class="ex_highlight_row">
						        <div id="container" class="overhead_container">
						        	<div id="button_area" class="inner_buttons">
						        	</div>
						            <div id="dynamic">
						                <table class="display" id="recomendation">
						                    <thead>
						                        <tr id="datatable_header">
						                            <th>ID</th>
						                            <th style="width: 100%">თარიღი</th>
						                            <th class="min">მინ. დრო</th>
													<th class="min">მაქს. დრო</th>
													<th class="min">თავისუფალი დრო</th>
						                        </tr>
						                    </thead>
						                    <thead>
						                        <tr class="search_header">
						                            <th class="colum_hidden">
														<input type="text" name="search_date" value="ფილტრი" class="search_init" />
						                            </th>
						                            <th>
						                            	<input type="text" name="search_date" value="ფილტრი" class="search_init" />
						                            </th>
						                            <th>
						                            	<input type="text" name="search_time" value="ფილტრი" class="search_init" />
													</th>
						                            <th>
						                            	<input type="text" name="search_free_time" value="ფილტრი" class="search_init" />
						                            </th>
													<th>
						                            	<input type="text" name="search_free_time" value="ფილტრი" class="search_init" />
						                            </th>
						                        </tr>
						                    </thead>
						                </table>
						            </div>
						        </div>
						    </div>
						</div>
				</fieldset>
			    <fieldset style="margin-top: 10px;">
					<legend>ბარათების შესრულების გეგმა</legend>
					    <div class="inner-table">
						    <div id="dt_example" class="ex_highlight_row">
						        <div id="container" class="overhead_container">
						        	<div id="button_area" class="inner_buttons">
						        	</div>
						            <div id="dynamic">
						                <table class="display" id="paln_end_date_list">
						                    <thead>
						                        <tr id="datatable_header">
						                            <th>ID</th>
						                            <th class="min">თარიღი</th>
						                            <th class="min">დრო</th>
						                            <th class="min"><pre>თავისუფალი
შუალედი</pre></th>
						                            <th style="width: 55px;">რ-ბა</th>
						                            <th style="width: 100%">კლიენტი</th>
						                        </tr>
						                    </thead>
						                    <thead>
						                        <tr class="search_header">
						                            <th class="colum_hidden">
						                            	<input type="text" name="search_id" value="ფილტრი" class="search_init" />
						                            </th>
						                            <th>
						                            	<input type="text" name="search_date" value="ფილტრი" class="search_init" />
						                            </th>
						                            <th>
						                            	<input type="text" name="search_time" value="ფილტრი" class="search_init" />
													</th>
						                            <th>
						                            	<input type="text" name="search_free_time" value="ფილტრი" class="search_init" />
						                            </th>
						                            <th>
													</th>
						                            <th>
						                            	<input type="text" name="search_clinet" value="ფილტრი" class="search_init" />
						                            </th>
						                        </tr>
						                    </thead>
						                </table>
						            </div>
						        </div>
						    </div>
						</div>
		        </fieldset>
			<div>';
	return $data;
}

function GetPage($res = '')
{
	$number = $res[number];
	if (empty($number)) {
		$number = time();
	}
	$data = '

		<div id="dialog-form">
		    <fieldset>
		    	<legend>ძირითადი ინფორმაცია</legend>
				<table width="100%" class="dialog-form-table">
					<tr>
						<th><label for="client_object">ობიექტი</label></th>
						<th><label for="responsible_person">პასუხისმგებელი პირი</label></th>
						<th><label for="car_id">ა/მ ნომერი</label></th>
					</tr>
					<tr>
						<th>
							<div class="seoy-row" id="client_object_seoy">
								<input type="text" id="client_object" class="idle seoy-large" onblur="this.className=\'idle seoy-large\'" onfocus="this.className=\'activeField seoy-large\'" value="' . $res['object_name'] . '" />
								<button id="client_object_btn" class="combobox">client_object</button>
							</div>
						</th>
						<th>
							<select id="receiver" class="idls">'. GetPersons($res[receiver]) .'</select>
						</th>
						<th>
							<select id="car_id" class="idls user_id">'. GetAutoNumber($res[car_id]) .'</select>
						</th>
						<th style="padding-left: 5px;">
							<button type="button" id="show_same_letters" class="ui-button ui-widget ui-state-default ui-corner-all ui-button-text-only ui-state-hover" role="button" aria-disabled="false">
									<span class="ui-button-text">მსგავსი ბარათები</span>
							</button>
						</th>
					</tr>
				</table>
				<table width="100%" class="dialog-form-table">
					<tr>
						<th><label for="source">წყარო</label></th>
						<th><label for="request_date">შეკვეთის თარიღი</label></th>
						<th><label for="end_date">მიტანის დრო</label></th>
						<th><label for="planed_end_date">შესრულების გეგმიური დრო</label></th>
						<th><label for="priority">პრიორიტეტი</label></th>

					</tr>
					<tr>
						<th><select id="source" class="idls date_time">'. GetSource($res[source]) .'</select></th>
						<th><input id="request_date" type="text" class="idle date_time" onblur="this.className=\'idle date_time\'" onfocus="this.className=\'activeField date_time\'" value="' . $res['request_date'] . '" /></th>
						<th><input id="end_date" type="text" class="idle date_time" onblur="this.className=\'idle date_time\'" onfocus="this.className=\'activeField date_time\'" value="' . $res['end_date'] . '" /></th>
						<th><input id="planed_end_date" type="text" class="idle date_time" onblur="this.className=\'idle date_time\'" onfocus="this.className=\'activeField date_time\'" value="' . $res['planned_end_date'] . '" style=" display: inline; "/>
							<button type="button" id="show_planed_end_date" class="ui-button ui-widget ui-state-default ui-corner-all ui-button-text-only ui-state-hover" role="button" aria-disabled="false" style=" height: 25px; width: 20px">
							<span class="ui-button-icon-primary ui-icon ui-icon-triangle-1-s"></span>
							</button>
						</th>
						<th><select id="priority" class="idls date_time">'. GetPriority($res[priority])  .'</select></th>
					</tr>
					<tr>
						<th><label for="job_start_date">მუშაობის დაწყების დრო</label></th>
						<th><label for="job_end_date">მუშაობის დასრულების დრო</label></th>
						<th><label for="fact_end_date">შესრულების ფაქტიური დრო</label></th>
						<th><label for="finish_date">ზედნადების დასრულების დრო</label></th>
					</tr>
					<tr>
						<th><input disabled="disabled" id="job_start_date" type="text" class="idle date_time" onblur="this.className=\'idle date_time\'" onfocus="this.className=\'activeField date_time\'" value="' . $res['job_start_date'] . '" /></th>
						<th><input disabled="disabled" id="job_end_date" type="text" class="idle date_time" onblur="this.className=\'idle date_time\'" onfocus="this.className=\'activeField date_time\'" value="' . $res['job_end_date'] . '" /></th>
						<th><input disabled="disabled" id="fact_end_date" type="text" class="idle date_time" onblur="this.className=\'idle date_time\'" onfocus="this.className=\'activeField date_time\'" value="' . $res['fact_end_date'] . '" /></th>
						<th><input disabled="disabled" id="finish_date" type="text" class="idle date_time" onblur="this.className=\'idle date_time\'" onfocus="this.className=\'activeField date_time\'" value="' . $res['finish_date'] . '" style="display: inline;"/></th>
					    <th><input id="overhead_check" style="display:inline-block; height: 13px;" type="checkbox" '.(($res[overhead_id]=='0')? "checked":"") .'>
						    <label for="overhead_check" id="overhead_label" style="">ზედ. გარეშე</label>
						</th>
					</tr>
				</table>
	        </fieldset>

			<div class="clear"></div>

			<fieldset style="width: 300px; float: left;">
		    	<legend>სერვისი</legend>

			    <div class="inner-table">
				    <div id="dt_example" class="ex_highlight_row">
				        <div id="container" class="overhead_container">
				        	<div id="button_area" class="inner_buttons">
				        		<button id="add_button_serv">დამატება</button><button id="delete_button_serv">წაშლა</button>
				        	</div>
				            <div id="dynamic">
				                <table class="display" id="service_list">
				                    <thead>
				                        <tr id="datatable_header">
				                            <th>ID</th>
				                            <th style="width: 300px">მოდელი</th>
				                            <th style="width: 90px">რაოდ.</th>
				                            <th class="check">#</th>
				                        </tr>
				                    </thead>
				                    <thead>
				                        <tr class="search_header">
				                            <th class="colum_hidden">
				                            	<input type="text" name="search_id" value="ფილტრი" class="search_init" />
				                            </th>
				                            <th>
				                            	<input type="text" name="search_prod" value="ფილტრი" class="search_init" />
				                            </th>
				                            <th></th>
											<th>
												<input type="checkbox" name="check-all-serv" id="check-all-serv">
											</th>
				                        </tr>
				                    </thead>
				                </table>
				            </div>
				        </div>
				    </div>
				</div>
			</fieldset>
			<fieldset style="float: right; width: 300px;">
		    	<legend>საგარანტიო</legend>

			    <div class="inner-table">
				    <div id="dt_example" class="ex_highlight_row">
				        <div id="container" class="overhead_container">
				        	<div id="button_area" class="inner_buttons">
								<button id="add_button_guar">დამატება</button><button id="delete_button_guar">წაშლა</button>
							</div>
				            <div id="dynamic">
				                <table class="display" id="guarante_list">
				                    <thead>
				                        <tr id="datatable_header">
				                            <th>ID</th>
				                            <th style="width: 240px">მოდელი</th>
				                            <th style="width: 150px">შტრიხკოდი</th>
											<th class="check">#</th>
				                        </tr>
				                    </thead>
				                    <thead>
				                        <tr class="search_header">
				                            <th class="colum_hidden">
				                            	<input type="text" name="search_id" value="ფილტრი" class="search_init" />
				                            </th>
				                            <th>
				                            	<input type="text" name="search_prod" value="ფილტრი" class="search_init" />
				                            </th>
				                            <th></th>
											<th>
												<input type="checkbox" name="check-all-guar" id="check-all-guar">
											</th>
				                        </tr>
				                    </thead>
				                </table>
				            </div>
				        </div>
				    </div>
				</div>
			</fieldset>

			<div class="clear"></div>

			<fieldset style="width: 58%; float: left; margin-top: 6px; height: 66px;">
				<table width="100%" class="dialog-form-table">
					<tr>
						<th><label for="new_client_person">ახალი</label></th>
						<th><label for="requester">მიმწოდებელი</label></th>
						<th><label for="receiver">მიმღები</label></th>
					</tr>
					<tr>
						<td>
							<input type="checkbox" class="new_requester" class="idls" />
						</td>
						<td>
							<input id="new_requester" type="text" class="idle" onblur="this.className=\'idle\'" onfocus="this.className=\'activeField\'" style="display: none; width: 219px;" />
							<select id="requester" class="idls address">' . GetClientPersons($res['object_name'], $res['object_person_id']) . '</select>
						</td>
						<td>
							<select id="responsible_person" class="idls address">'. GetPersons($res[responsible_person]) .'</select>
						</td>
					</tr>
				</table>
			</fieldset>

			<fieldset style="width: 30%; float: right;">
		    	<legend>შენიშვნა</legend>

		    	<table class="dialog-form-table">
					<tr>
						<td valign="top">
							<textarea id="comment" class="idle large" cols="40" rows="3">' . $res['comment'] . '</textarea>
						</td>
					</tr>
				</table>
        	</fieldset>

			<!-- ID -->
			<input type="hidden" id="warehouse_id" value="' . $res['warehouse_id'] . '" />
			<input type="hidden" id="latter_id" value="' . $res['id'] . '" />
			<input type="hidden" id="overhead_id" value="' . $res['overhead_id'] . '" />
			<input type="hidden" id="req_id" value="' . $_REQUEST[id]. '" />
			<input type="hidden" id="client_ob_trans"  value="' . $res[transporter_company_id]. '" /> 
            <input type="hidden" id="client_objects_id"  value="' . $res[client_objects_id]. '" />     
	    </div>';

	return $data;
}
function check(){
	$rResult = mysql_query("SELECT
							CASE WHEN `a_letter`.`status` = 4 THEN CONCAT('შეფუთულია შეკვეთა ', client.`name`,'(',client_objects.`name`, ') -სგან')
							END AS 'notify'
							FROM     	`a_letter`
							LEFT JOIN	`client_objects` ON `client_objects`.`id` = `a_letter`.`client_object_id`
							LEFT JOIN	`client`       ON `client`.`id` = `client_objects`.`client_id`
							WHERE		`a_letter`.`status` in (4)
							ORDER BY	`a_letter`.`planned_end_date` ASC
							LIMIT 1");
	$aRow = mysql_fetch_array( $rResult );
	return $aRow[0];
}
?>